/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import entity.EventInfo;
import entity.UserInfo;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import util.LogUtil;

/**
 *
 * @author VietTien
 */
@Stateless
public class EventInfoFacade extends AbstractFacade<EventInfo> {

    @PersistenceContext(unitName = "BluePumpkin-ejbPU")
    private EntityManager em;

    protected EntityManager getEntityManager() {
        return em;
    }

    public EventInfoFacade() {
        super(EventInfo.class);
    }

    public List<EventInfo> showEventReg() {
        String sql = "SELECT e FROM EventInfo e WHERE e.endReg > :endReg ORDER BY e.endReg DESC";
        Query query = em.createQuery(sql);
        query.setParameter("endReg", new Date());
        List<EventInfo> list = query.getResultList();
        return list;
    }

    public List<EventInfo> showEvent() {
        String sql = "SELECT e FROM EventInfo e ORDER BY e.eventID DESC";
        Query query = em.createQuery(sql);
        List<EventInfo> list = query.getResultList();
        return list;
    }

  
    public List<EventInfo> showHomeEvent() {
        String sql = "SELECT e FROM EventInfo e Where e.isDelete = :isDelete ORDER BY e.eventID DESC";
        Query query = em.createQuery(sql);
        query.setParameter("isDelete", Boolean.FALSE);
        List<EventInfo> list = query.getResultList();
        return list;
    }

    public List<EventInfo> getEventReg() {
        String sql = "SELECT DISTINCT e FROM EventInfo e, Registration r Where e = r.eventInfo and e.isDelete = :isDelete ORDER BY e.eventID DESC";
        Query query = em.createQuery(sql);
        query.setParameter("isDelete", Boolean.FALSE);
        List<EventInfo> list = query.getResultList();
        System.out.println("LIST REG EVT: " + list.size());
        return list;
    }

    //show list of events has not been started yet
    public List<EventInfo> showUpcommingEvent() {
        String sql = "SELECT e FROM EventInfo e Where e.isDelete = :isDelete AND e.endReg >= :today ORDER BY e.endReg DESC";
        Query query = em.createQuery(sql);
        query.setParameter("isDelete", Boolean.FALSE);
        query.setParameter("today", new java.util.Date());
        List<EventInfo> list = query.getResultList();
        return list;
    }

    public List<EventInfo> findByName(String name){
        Query query = em.createNamedQuery("EventInfo.findByEventName");
        query.setParameter("eventName", name);
        return query.getResultList();
    }

     public List<EventInfo> findByNameExceptOne(String oldName, String newName){
        Query query = em.createQuery("SELECT e FROM EventInfo e WHERE e.eventName <> :old AND e.eventName = :new");
        query.setParameter("old", oldName);
        query.setParameter("new", newName);
        return query.getResultList();
    }
    //show list of events had end 30 day ago
    public List<EventInfo> showCompletedEvent() {
        Date now = new java.util.Date();
        SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd");
        String sql = "SELECT [EventID] as 'EVENTID',[EventName],[ImageEvent],[EventTitle],[DescriptionEvent] ,[MinPlayer],[MaxPlayer] ,[StartEvent],[FinishEvent],[StartReg],[EndReg],[isDelete] FROM EventInfo e Where e.isDelete = 0 AND  DATEDIFF(DAY,e.FinishEvent,'"+ s.format(now)  +"') >= 3";
        List<EventInfo> list = null;
        try {
            list = em.createNativeQuery(sql,EventInfo.class).getResultList();
        } catch (Exception e) {
            LogUtil.doLog(e);
            
        }
          return list;
    }

}
